package edu.ccut.saturn.operation.database;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import edu.ccut.saturn.component.IDataInfo;
import edu.ccut.saturn.component.IDataItem;
import edu.ccut.saturn.component.ILogicContext;
import edu.ccut.saturn.component.IParameter;
import edu.ccut.saturn.component.IParameterContext;
import edu.ccut.saturn.component.SaturnComponentException;
import edu.ccut.saturn.component.SaturnData;
import edu.ccut.saturn.component.SaturnDataManager;

public abstract class AbstractDatabaseOperation {
	protected final static int PRIMARY_KEY_TYPE = 1;
	protected final static int RANDOM_TYPE = 2;
	protected final static int NON_CONDITION_TYPE = 0;

	/**
	 * blobs与clobs的Object[]{0表名，1主健名，2主键值，3blob名，4blob值,5whereCondition}
	 * 
	 * @param args
	 *            Object[]{0链接，1whereCondition,2,blob容器，3clob容器，4parameterContext}
	 */

	protected abstract String getSql(SaturnData saturnData, Object[] args)
			throws Exception;

	/**
	 * 定义得到SaturnData的方式，已经维护了两个模板: getSaturnDataByContext,getSaturnDataByFullKey
	 * 
	 * @param parameterContext
	 * @return
	 * @throws Exception
	 */
	protected abstract SaturnData getSaturnData(IParameterContext parameterContext)
			throws Exception;

	/**
	 * 设置返回参数位置
	 * 
	 * @return
	 */
	protected abstract int getOutParamIndex();

	/**
	 * 告知系统此ｓｑｌ加入何种where条件,
	 * 
	 * @return
	 */
	protected abstract int getConditionType();

	protected void executeUpdate(IParameterContext parameterContext,Connection conn)
			throws Exception {
		List<Object[]> blobs = new ArrayList<Object[]>();
		List<Object[]> clobs = new ArrayList<Object[]>();
		String sql = null;
		StringBuffer whereCondition = new StringBuffer();
		SaturnData saturnData = getSaturnData(parameterContext);

		// 通过saturndata有无来判断sql产生方式
		if (saturnData != null) {
			switch (getConditionType()) {
			case PRIMARY_KEY_TYPE:
				whereCondition.append(((String) parameterContext
						.getParameterValue(1)).trim());
				IDataItem item = getPrimeKeyDataItem(saturnData);
				if (item == null)
					throw new SaturnComponentException(
							"This dataObject doesn't have a PrimaryKey."); //$NON-NLS-1$
				String keyValue = whereCondition.toString();
				whereCondition.delete(0, whereCondition.length());
				whereCondition.append(" WHERE ").append(item.getColumn()) //$NON-NLS-1$
						.append("='").append(keyValue).append("' "); //$NON-NLS-1$ //$NON-NLS-2$
				break;
			case RANDOM_TYPE:
				if ((String) parameterContext.getParameterValue(1) != null) {
					whereCondition.append(" WHERE 1=1 ").append((String) parameterContext
							.getParameterValue(1));
				} else {
					whereCondition.append(""); //$NON-NLS-1$
				}
				break;
			default:
				break;
			}
			sql = getSql(saturnData, new Object[] { conn,
					whereCondition.toString(), blobs, clobs, parameterContext });
		} else {
			sql = (String) parameterContext.getParameterValue(0);
		}
		if (sql != null) {
			int count = doUpdate(conn, sql, blobs, clobs);
			parameterContext.setParameter(getOutParamIndex(), count);
		}
	}

	protected SaturnData getSaturnDataByContext(IParameterContext parameterContext)
			throws SaturnComponentException {
		SaturnData saturnData = (SaturnData) parameterContext.getParameterValue(0);
		if (saturnData == null) {
			// TODO 抛出异常
			throw new SaturnComponentException("Saturn data parameter is null."); //$NON-NLS-1$
		}
		return saturnData;
	}

	protected SaturnData getSaturnDataByFullKey(IParameterContext parameterContext)
			throws SaturnComponentException {
		String fullKey = (String) parameterContext.getParameterValue(0);
		SaturnData saturnData = SaturnDataManager.getInstance()
				.createData(fullKey);
		if (saturnData == null) {
			// TODO 抛出异常
			throw new SaturnComponentException("Saturn data parameter is null."); //$NON-NLS-1$
		}
		return saturnData;
	}

	private int doUpdate(Connection conn, String sql, List<Object[]> blobs,
			List<Object[]> clobs) throws Exception, SQLException, IOException,
			SaturnComponentException {
		Statement stat = null;
		ResultSet rs = null;
		BufferedOutputStream outStream = null;
		BufferedWriter writer = null;
		int count = 0;
		if (conn != null) {
			try {
				stat = conn.createStatement();
				// 判断是否需要处理blob
				if (blobs.isEmpty() && clobs.isEmpty()) {
					count = stat.executeUpdate(sql);
				} else {
					// 默认自动提交，那么说明没有走事务
					boolean isDefaultCommit = true;
					isDefaultCommit = conn.getAutoCommit();
					if (isDefaultCommit) {
						conn.setAutoCommit(false);
					}
					count = stat.executeUpdate(sql);
					if (!blobs.isEmpty()) {
						// 拼查询语句
						 rs = stat
								.executeQuery(getSelectBlobSql(blobs));
						while (rs.next()) {
							for (Object[] blobinfo : blobs) {
								oracle.sql.BLOB blob = (oracle.sql.BLOB) rs
										.getBlob((String) blobinfo[3]);
//								 outStream = (BufferedOutputStream) blob.setBinaryStream(0);
								outStream = new BufferedOutputStream(blob
										.getBinaryOutputStream());// 不用这个方法在tomcat下出错：没有setBinaryStream特性
								byte[] blobvalue = (byte[]) blobinfo[4];
								outStream.write(blobvalue, 0, blobvalue.length);
								outStream.flush();
							}
						}
					}
					if (!clobs.isEmpty()) {
						// 拼查询语句
						 rs = stat
								.executeQuery(getSelectClobSql(clobs));
						while (rs.next()) {

							for (Object[] clobinfo : clobs) {
								oracle.sql.CLOB clob = (oracle.sql.CLOB) rs
										.getClob((String) clobinfo[3]);
//								 writer = (BufferedWriter) clob.setCharacterStream(0);
								writer = new BufferedWriter(clob
										.getCharacterOutputStream());// 不用这个方法在tomcat下出错：没有setCharacterStream特性
								String clobvalue = (String) clobinfo[4];
								char[] c = clobvalue.toCharArray();
								writer.write(c, 0, c.length);
								writer.flush();
							}
						}
					}
					// 如果原提交方式为自动提交，就显示提交
					if (isDefaultCommit) {
						conn.commit();
						conn.setAutoCommit(true);
					}
				}

			} catch (Exception e) {
				e.printStackTrace();
				throw new SaturnComponentException("insert exception");
			} finally {
                if(rs != null){
                	try {
                		rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
						throw new SaturnComponentException(
								"ResultSet close Exception");
					}
                }
				if (stat != null) {
					try {
						stat.close();
					} catch (SQLException e) {
						e.printStackTrace();
						throw new SaturnComponentException(
								"Statment close Exception");
					}
				}
				if (outStream != null) {
					try {
						outStream.close();
					} catch (RuntimeException e) {
						e.printStackTrace();
						throw new SaturnComponentException(
								"outStream close Exception");
					}
				}
				if (writer != null) {
					try {
						writer.close();
					} catch (RuntimeException e) {
						e.printStackTrace();
						throw new SaturnComponentException(
								"writer close Exception");
					}

				}
			}
		} else {
			throw new SaturnComponentException(
					"Can not get Connection in database component"); //$NON-NLS-1$
		}
		return count;
	}
    public static void main(String[] args) {
		String d= "";
		System.out.println(d.getClass());;
	}
	protected void executeQuery(IParameterContext parameterContext,Connection conn)
			throws Exception {
		SaturnData saturnData = getSaturnData(parameterContext);
		//得到数据项中的sqltext
		String tempSQL = saturnData.getSaturnDataInfo().getSqlStr();
		String value = (String) parameterContext.getParameterValue(1);
		StringBuffer whereCond = new StringBuffer();
		List<SaturnData> list = null;
		switch (getConditionType()) {
		case NON_CONDITION_TYPE:
			if (value != null && !"".equals(value)) { //$NON-NLS-1$
				//解析SQL
				value = parseCondtion(value, (ILogicContext)parameterContext.getParameterValue(3));
				list = doQuery(conn, saturnData, value);// value is sql
				//add by yanshuang begin
				IParameter parameter= parameterContext.getParameter(getOutParamIndex());
				if("java.util.ArrayList".equals(parameter.getClassType().getName())){
					parameterContext.setParameter(getOutParamIndex(), list);
				}else{
					if(list==null)
					    parameterContext.setParameter(getOutParamIndex(), null);
					else
						parameterContext.setParameter(getOutParamIndex(), list.get(0));
				}
				//add end
			}else if( tempSQL!=null&&!tempSQL.equals("")){
				list = doQuery(conn, saturnData, tempSQL);// value is sql
				//add by yanshuang begin
				IParameter parameter= parameterContext.getParameter(getOutParamIndex());
				if("java.util.ArrayList".equals(parameter.getClassType().getName())){
					parameterContext.setParameter(getOutParamIndex(), list);
				}else{
					if(list==null)
					    parameterContext.setParameter(getOutParamIndex(), null);
					else
						parameterContext.setParameter(getOutParamIndex(), list.get(0));
				}
				//add end
			}else {
				throw new SaturnComponentException("SQL parameter is null."); //$NON-NLS-1$
			}
			break;
		case PRIMARY_KEY_TYPE:
			if (value != null && !"".equals(value)) { //$NON-NLS-1$
				IDataItem item = getPrimeKeyDataItem(saturnData);
				if (item == null)
					throw new SaturnComponentException(
							"This dataObject doesn't have a PrimaryKey."); //$NON-NLS-1$
				whereCond.append(" WHERE ").append( //$NON-NLS-1$
						item.getColumn()).append("='").append(value)// value is
						// primary
						// key
						// //$NON-NLS-1$
						.append("' "); //$NON-NLS-1$
				if(tempSQL!=null&&!tempSQL.equals("")){
					//在sqlText后拼接whereCondition
					value=tempSQL+whereCond.toString();
				}else{
					value = getSql(saturnData, new Object[] { whereCond.toString() });
				}
				list = doQuery(conn, saturnData, value);
				if (list != null) {
					if (!list.isEmpty()) {
						parameterContext.setParameter(getOutParamIndex(),
								(SaturnData) list.get(0));
					}
				}
			} else {
				throw new SaturnComponentException(
						"primarykey parameter is null."); //$NON-NLS-1$
			}
			break;
		case RANDOM_TYPE:
			if (value == null)
				value = ""; //$NON-NLS-1$
			whereCond.append(value);// value is whereCondition
			if(tempSQL!=null&&!tempSQL.equals("")){
				value=tempSQL+whereCond.toString();
			}else{
				value = getSql(saturnData, new Object[] { " WHERE 1=1 "+whereCond.toString() });
			}
			list = doQuery(conn, saturnData, value);
			//add by yanshuang begin
			IParameter parameter= parameterContext.getParameter(getOutParamIndex());
			if("java.util.ArrayList".equals(parameter.getClassType().getName())){
				parameterContext.setParameter(getOutParamIndex(), list);
			}else{
				if(list==null)
				    parameterContext.setParameter(getOutParamIndex(), null);
				else
					parameterContext.setParameter(getOutParamIndex(), list.get(0));
			}
			//add end
		default:
			break;
		}
	}

	protected void executeQueryByPageAndCondition(
			IParameterContext parameterContext,Connection conn) throws Exception {
		//分页查询暂时不能支持sqlText
		SaturnData saturnData = getSaturnData(parameterContext);
		//得到数据项中的sqltext
		String tempSQL = saturnData.getSaturnDataInfo().getSqlStr();
		String sql = null;
		int start = 0;
		int end = 0;
		String whereCondition = null;
		whereCondition = (String) parameterContext.getParameterValue(1);
		start = (Integer) parameterContext.getParameterValue(2);
		end = (Integer) parameterContext.getParameterValue(3);
		sql = this.getSql(saturnData,
				new Object[] { start, end, whereCondition,tempSQL });
		if (sql != null && !"".equals(sql)) { //$NON-NLS-1$
			List<SaturnData> list = doQuery(conn, saturnData, sql);
			parameterContext.setParameter(getOutParamIndex(), list);
		}
	}

	/**
	 * 
	 * @param conn
	 * @param saturnData
	 * @param sql
	 * @throws SQLException
	 * @throws SaturnComponentException
	 */
	private List<SaturnData> doQuery(Connection conn, SaturnData saturnData,
			String sql) throws SQLException, SaturnComponentException {
		Statement stat = null;
		ResultSet rs = null;
		List<SaturnData> list = new ArrayList<SaturnData>();
		if (conn != null) {
			try {
				stat = conn.createStatement();
				rs = stat.executeQuery(sql);
				list = setListValueFromRs(saturnData, rs);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SaturnComponentException("query Exception");
			} finally {
				if (rs != null) {
					try {
						rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
						throw new SaturnComponentException(
								"ResultSet close exception");
					}
				}
				if (stat != null) {
					try {
						stat.close();
					} catch (SQLException e) {
						e.printStackTrace();
						throw new SaturnComponentException(
								"Statment close exception");
					}
				}
			}
		} else {
			throw new SaturnComponentException(
					"Can not get Connection in database component"); //$NON-NLS-1$
		}
		return list;
	}

	/**
	 * 返回主键
	 * 
	 * @param saturnData
	 * @return
	 */
	protected IDataItem getPrimeKeyDataItem(SaturnData saturnData) {
		IDataInfo dataInfo = saturnData.getSaturnDataInfo();
		Set<String> keys = dataInfo.keySet();

		for (String key : keys) {
			IDataItem dataItem = dataInfo.getDataItem(key);

			if ("true".equals(dataItem.getIsPrimaryKey())) { //$NON-NLS-1$
				return dataItem;
			}
		}
		return null;
	}

	/**
	 * 将ResultSet中的查询结果封装到list
	 * 
	 * @param data
	 * @param rs
	 * @return
	 * @throws Exception
	 */
	private List<SaturnData> setListValueFromRs(SaturnData data, ResultSet rs)
			throws Exception {
		IDataInfo dataInfo = data.getSaturnDataInfo();
		// Object[] keys = dataInfo.keySet().toArray();
		List<SaturnData> list = new ArrayList<SaturnData>();
		String packagename = dataInfo.getSpace();
		String getClassName = dataInfo.getType();
		// 得到查询结果的列数和列名称
		int columnCount = rs.getMetaData().getColumnCount();
		String[] columnNames = new String[columnCount];
		for (int i = 0; i < columnCount; i++) {
			columnNames[i] = rs.getMetaData().getColumnName(i+1);
		}
		//存放真正需要处理的key
		ArrayList<String> usedKeyArr = new ArrayList<String>();
		for(String key:dataInfo.keySet()){
			for(String column :columnNames){
				if(column.equalsIgnoreCase(key))
					usedKeyArr.add(key);
			}
		}
		SaturnData saturnData = null;
		while (rs.next()) {
			saturnData = SaturnDataManager.getInstance().createData(packagename,
					getClassName);
			for (int i = 0; i < usedKeyArr.size(); i++) {
				// 名称要变换为区分大小写的格式
				String usedKey =usedKeyArr.get(i);
				IDataItem dataItem = dataInfo.getDataItem(usedKey);
				if (dataItem != null) {
					String type = dataItem.getType();
					String javatype = dataItem.getJavaType();
					if ("BLOB".equals(type)) { //$NON-NLS-1$
						BufferedInputStream is = null;
						byte[] value = null;
						try {
							oracle.sql.BLOB blob = (oracle.sql.BLOB) rs
									.getBlob(usedKey);
							if (blob != null) {
								is = new BufferedInputStream(blob
										.getBinaryStream());
								value = new byte[(int) blob.length()];
								is.read(value);
							}
						} catch (Exception e) {
							e.printStackTrace();
							throw new SaturnComponentException("BLOB Exception");
						} finally {
							try {
								if (is != null)
									is.close();
							} catch (Exception e) {
								e.printStackTrace();
								throw new SaturnComponentException(
										"InputStream close exception");
							}
						}
						saturnData.set(usedKey, value);
					} else if ("CLOB".equals(type)) { //$NON-NLS-1$
						BufferedReader reader = null;
						char[] charValue = null;
						String value = null;
						try {
							oracle.sql.CLOB clob = (oracle.sql.CLOB) rs
									.getClob(usedKey);
							if (clob != null) {
								reader = new BufferedReader(clob
										.getCharacterStream());
								charValue = new char[(int) clob.length()];
								reader.read(charValue);
								value = new String(charValue);
							}
						} catch (Exception e) {
							e.printStackTrace();
							throw new SaturnComponentException("CLOB exception");
						} finally {
							try {
								if (reader != null)
									reader.close();
							} catch (Exception e) {
								e.printStackTrace();
								throw new SaturnComponentException(
										"reader close exception");
							}
						}
						saturnData.set(usedKey, value);
					} else {
						setSaturnData(saturnData, usedKey, rs, javatype);
					}
				}
			}
			list.add(saturnData);
		}
		if (list.size() == 0) {
			return null;
		}
		return list;
	}

	/**
	 * 从rs中得到数据 并放到saturnData中，大数据处理除外
	 * 
	 * @param saturnData
	 *            容器
	 * @param key
	 *            键
	 * @param rs
	 *            值
	 * @param dataObjectType
	 *            判断条件
	 * @throws Exception
	 */
	private void setSaturnData(SaturnData saturnData, String key, ResultSet rs,
			String javaType) throws Exception {
		if ("java.lang.Integer".equals(javaType)) { //$NON-NLS-1$
			saturnData.set(key, rs.getInt(key));// TODO BigDecimal Integer 转换问题
		} else if ("java.lang.Double".equals(javaType)) {
			saturnData.set(key, rs.getDouble(key));// TODO BigDecimal Double
													// 转换问题
		} else if ("java.lang.Float".equals(javaType)) {
			saturnData.set(key, rs.getFloat(key));// TODO BigDecimal Float
													// 转换问题
		} else if ("java.math.BigDecimal".equals(javaType)) {
			saturnData.set(key, rs.getBigDecimal(key));
		} else if ("java.lang.Short".equals(javaType)) {
			saturnData.set(key, rs.getShort(key));
		} else if ("java.lang.Long".equals(javaType)) {
			saturnData.set(key, rs.getLong(key));
		} else if ("java.sql.Date".equals(javaType)) {
			saturnData.set(key, rs.getDate(key));
		} else if ("java.lang.String".equals(javaType)) {
			saturnData.setString(key, rs.getString(key));
		} else if ("byte".equals(javaType)) {
			saturnData.set(key, rs.getByte(key));
		} else {
			saturnData.set(key, rs.getObject(key));
		}
	}

	/**
	 * 得到查询大数据sql
	 * 
	 * @param borc
	 *            blobs or clobs
	 * @return
	 */
	private String getBorCSql(List<Object[]> borc) {
		StringBuffer sql = new StringBuffer();
		StringBuffer names = new StringBuffer(""); //$NON-NLS-1$
		String tableName = null;
		String primaryKey = null;
		Object primaryKeyValue = null;
		StringBuffer whereCondition = new StringBuffer();
		for (Object[] info : borc) {
			tableName = (String) info[0];
			primaryKey = (String) info[1];
			primaryKeyValue = info[2];
			names.append(info[3]).append(","); //$NON-NLS-1$
			if (info[5] != null)
				if (whereCondition.length() == 0)
					whereCondition.append((String) info[5]);
		}
		names.deleteCharAt(names.length() - 1);
		if (whereCondition.length() == 0)
			whereCondition
					.append(" WHERE ").append(primaryKey).append("='").append(primaryKeyValue) //$NON-NLS-1$ //$NON-NLS-2$
					.append("'"); //$NON-NLS-1$
		// for update 锁表
		sql
				.append("SELECT ").append(names).append(" FROM ").append(tableName).append(" ").append(whereCondition) //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				.append(" for update"); //$NON-NLS-1$
		return sql.toString();
	}

	private String getSelectBlobSql(List<Object[]> blobs) {
		return getBorCSql(blobs);
	}

	private String getSelectClobSql(List<Object[]> clobs) {
		return getBorCSql(clobs);
	}

	protected boolean isNullorZero(Object value) {
		if (value == null) {
			return true;
		} else {
			if (value instanceof Double || value instanceof Integer
					|| value instanceof Float || value instanceof Short
					|| value instanceof Long) {
				if (value.equals(0)) {
					return true;
				}
			}
		}
		return false;
	}
	protected String parseCondtion(String condition, ILogicContext context) {
		StringBuffer newCondition =new StringBuffer().append(""); //$NON-NLS-1$
		char[] charArray = condition.toCharArray();
	    StringBuffer oldexp=new StringBuffer();//${}表达式
	    boolean isGetExp = false; //是否在拼${}表达式
	    boolean is$ = false;
	    for(char currItem:charArray){
	    	if(is$){
	    		if(currItem=='{'){
	    			//开始拼表达式
	    			isGetExp=true;
	    			oldexp.append("${"); //$NON-NLS-1$
	    		}else{
	    			//只是普通$符号
	    			newCondition.append("$").append(currItem); //$NON-NLS-1$
	    		}
	    		is$=false;
	    	}else{
	    		if(isGetExp){
	    			if(currItem!='}'){
	    				oldexp.append(currItem);
	    			}else{
	    				oldexp.append(currItem);
	    				isGetExp=false;
	    				//解析
	    				Object value = context.getValue(oldexp.toString());
	    				if(value!=null&&!value.equals("")){
	    					newCondition.append(value);
	    				}else{
	    					newCondition.append(""); //$NON-NLS-1$
	    				}
	    				oldexp.delete(0, oldexp.length());
	    			}
		    	}else{
		    		if(currItem!='$'){
		    			newCondition.append(currItem);
		    		}else{
		    			//判断是否开始拼表达式
		    			is$=true;
		    		}
		    	}	
	    	}
	    }
		return newCondition.toString();
	}
}
